package com.zjtaixue.usersite.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.zjtaixue.usersite.domain.Class;
import com.zjtaixue.usersite.domain.*;
import com.zjtaixue.usersite.exception.ApplicationException;
import com.zjtaixue.usersite.mapper.*;
import com.zjtaixue.usersite.page.PageResult;
import com.zjtaixue.usersite.query.StudentQueryObject;
import com.zjtaixue.usersite.service.StudentService;
import com.zjtaixue.usersite.util.MD5Util;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;

import static com.zjtaixue.usersite.domain.Login.STATE_ENABLE;
import static com.zjtaixue.usersite.domain.Login.TYPE_STUDENT;
@Slf4j
@Service("studentService")
public class StudentServiceImpl implements StudentService {

    @Autowired
    private StudentMapper studentMapper;
    @Autowired
    private MajorMapper majorMapper;
    @Autowired
    private ClassMapper classMapper;
    @Autowired
    private LoginMapper loginMapper;
    @Autowired
    private CollegeMapper collegeMapper;

    //总行数
    private int totalRows = 0;
    //总条数
    private int totalCells = 0;

    //获取总行数
    public int getTotalRows() {
        return totalRows;
    }

    //获取总列数
    public int getTotalCells() {
        return totalCells;
    }

    @Override
    public void insert(Student student) {
        int count = studentMapper.countByNumber(student.getNumber());
        if (count > 0) {
            throw new ApplicationException("已存在相同学号的学生！");
        }
        Class cla = classMapper.selectByPrimaryKey(student.getClassId());
        student.setEnrollmentYear(cla.getEnrollmentYear());
        studentMapper.insert(student);
        Login login = new Login();
        login.setUserinfoId(student.getId());
        login.setState(STATE_ENABLE);
        login.setUsername(student.getNumber());
        login.setPassword(MD5Util.encode("123456"));
        login.setType(TYPE_STUDENT);
        loginMapper.insert(login);
    }

    @Override
    public PageResult<JSONObject> listPageResult(StudentQueryObject qo) {
        int count = studentMapper.queryCount(qo);
        if (count > 0) {
            List<Student> students = studentMapper.query(qo);
            List<JSONObject> jsonObjects = new ArrayList<>();
            for (Student student : students) {
                JSONObject jsonObject = (JSONObject) JSON.toJSON(student);
                List<Login> logins = loginMapper.listByUserInfoId(student.getId(), (byte) 0);
                if (logins != null && logins.size() > 0) {
                    jsonObject.put("state", logins.get(0).getState());
                }
                Class cla = classMapper.selectByPrimaryKey(student.getClassId());
                jsonObject.put("class", cla);
                Major major = majorMapper.selectByPrimaryKey(cla.getMajorId());
                jsonObject.put("college", collegeMapper.selectByPrimaryKey(major.getCollegeId()));
                jsonObjects.add(jsonObject);
            }
            return new PageResult(jsonObjects, qo.getCurrentPage(), count, qo.getPageSize());
        }
        return PageResult.empty(qo.getPageSize());
    }

    @Override
    public Student getById(Integer id) {
        return studentMapper.selectByPrimaryKey(id);
    }

    @Override
    public void update(Student student) {
        int count = studentMapper.countOthersByNumber(student);
        if (count > 0) {
            throw new ApplicationException("已存在相同学号的学生");
        }
        Class cla = classMapper.selectByPrimaryKey(student.getClassId());
        student.setEnrollmentYear(cla.getEnrollmentYear());
        Login login = loginMapper.findByUserInfoAndType(student.getId(), TYPE_STUDENT);
        login.setUsername(student.getNumber());
        loginMapper.updateByPrimaryKey(login);
        studentMapper.updateByPrimaryKey(student);
    }

    @Override
    public int deleteById(Integer id) {
        loginMapper.deleteByUserInfoAndType(id, TYPE_STUDENT);
        return studentMapper.deleteByPrimaryKey(id);
    }

    @Override
    public List<Student> gainInfo(InputStream is) {
        List<Student> students = new ArrayList<Student>();
        Workbook wb = null;
        try {
            wb = new XSSFWorkbook(is);
            students = readValue(wb);
        } catch (Exception e) {
            e.printStackTrace();
            throw new ApplicationException(e.toString());
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    is = null;
                    e.printStackTrace();
                }
            }
        }
        return students;
    }

    @Override
    public List<Student> readValue(Workbook wb) throws ParseException {
        //得到第一个shell
        Sheet sheet = wb.getSheetAt(0);
        //得到Excel的行数
        this.totalRows = sheet.getLastRowNum() + 1;
        //获取列数
        //得到Excel的列数(前提是有行数)
        if (totalRows >= 1 && sheet.getRow(0) != null) {//判断行数大于一，并且第一行必须有标题（这里有bug若文件第一行没值就完了）
            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        } else {
            throw new ApplicationException("Excel表格格式不正确,请下载模板进行更改");
        }
        List<Student> students = new ArrayList<Student>();//声明一个对象集合
        //校验
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) continue;
            //循环Excel的列
            for (int c = 0; c < this.totalCells; c++) {
                Cell cell = row.getCell(c);
                if(cell==null)continue;
                switch (c) {
                    case 0://学院名
                        if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                            throw new ApplicationException("请您在Excel表中输入学院名称" + "\t(第" + (r + 1) + "行，" + "第" + (c + 1) + "列)");
                        }
                        break;
                    case 1://专业名
                        if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                            throw new ApplicationException("请您在Excel表中输入专业名称" + "\t(第" + (r + 1) + "行，" + "第" + (c + 1) + "列)");
                        }
                        break;
                    case 2://入学年份
                        if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                            throw new ApplicationException("请您在Excel表中填入入学年份!" + "\t(第" + (r + 1) + "行，" + "第" + (c + 1) + "列)");
                        }
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String yearStr = cell.getStringCellValue().trim();
                        if (!yearStr.matches("[0-9]+")) {
                            throw new ApplicationException("请您在Excel表中填入正确的入学年份!" + "\t(第" + (r + 1) + "行，" + "第" + (c + 1) + "列)");
                        }
                        break;
                    case 3://班级名
                        if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                            throw new ApplicationException("请您在Excel表中填入班级名称!" + "\t(第" + (r + 1) + "行，" + "第" + (c + 1) + "列)");
                        }
                        break;
                    case 4://学生姓名
                        if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                            throw new ApplicationException("请您在Excel表中填入学生姓名!" + "\t(第" + (r + 1) + "行，" + "第" + (c + 1) + "列)");
                        }
                        break;
                    case 5://学号--这里校验会有问题,如果同一张excel中存在相同的学生账号会无法校验出来
                        if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                            throw new ApplicationException("请您在Excel表中填入学号!" + "\t(第" + (r + 1) + "行，" + "第" + (c + 1) + "列)");
                        }
                        break;
                }
            }
        }
        //循环Excel行数,从第二行开始。标题不入库
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) continue;
            //暂存的信息
            int collegeId = 0,majorId = 0,classId=0;
            String entryYear="",studentName="",studentNumber="";
            for (int c = 0; c < this.totalCells; c++) {
                Cell cell = row.getCell(c);
                if(cell==null)continue;
                cell.setCellType(Cell.CELL_TYPE_STRING);
                switch (c) {
                    case 0: //学院
                        College college = collegeMapper.selectByCollegeName(cell.getStringCellValue().trim());
                        if (college == null) {
                            college=new College();
                            college.setName(cell.getStringCellValue().trim());
                            collegeMapper.insert(college);
                        }
                        collegeId = college.getId();
                        break;
                    case 1: //专业
                        Major major = majorMapper.selectByMajorNameAndCollegeId(cell.getStringCellValue().trim(),collegeId);
                        if(major==null){
                            major=new Major();
                            major.setName(cell.getStringCellValue().trim());
                            major.setCollegeId(collegeId);
                            majorMapper.insert(major);
                        }
                        majorId=major.getId();
                        break;
                    case 2: //入学年份
                        entryYear=cell.getStringCellValue().trim();
                        break;
                    case 3: //班级
                        Class cla=classMapper.selectByClassNameAndMajorId(cell.getStringCellValue().trim(),majorId);
                        if(cla==null){
                            cla=new Class();
                            cla.setName(cell.getStringCellValue().trim());
                            cla.setEnrollmentYear(Integer.valueOf(entryYear));
                            cla.setMajorId(majorId);
                            classMapper.insert(cla);
                        }
                        classId=cla.getId();
                        break;
                    case 4://姓名
                        studentName=cell.getStringCellValue().trim();
                        break;
                    case 5://学号
                        studentNumber=cell.getStringCellValue().trim();
                        log.info("学生的学号是:"+studentNumber);
                        break;
                }
            }
            Student student=studentMapper.selectByNumber(studentNumber);
            if(student==null){
                student=new Student();
                student.setEnrollmentYear(Integer.valueOf(entryYear));
                student.setClassId(classId);
                student.setNumber(studentNumber);
                student.setName(studentName);
                studentMapper.insert(student);
                //插入登入信息
                Login login=new Login();
                login.setUsername(studentNumber);
                login.setPassword(MD5Util.encode("123456"));
                login.setState(STATE_ENABLE);
                login.setType(TYPE_STUDENT);
                login.setUserinfoId(student.getId());
                loginMapper.insert(login);
                //添加对象到集合中
                students.add(student);
            }else{
                throw new ApplicationException("Excel中学号重复!" + "\t(第" + (r + 1) + "行，" + "第" + 6 + "列)\r\n"+
                        "重复的学号是:"+studentNumber);
            }
        }
        return students;
    }

    @Override
    public List<Student> listByClassId(Integer classId) {
        return studentMapper.listByClassId(classId);
    }

    @Override
    public List<Student> listByMajorId(Integer majorId) {
        return studentMapper.listByMajorId(majorId);
    }

    @Override
    public List<Student> listByKeyword(String keyword) {
        return studentMapper.listByKeyword(keyword);
    }

    @Override
    public List<Student> listAll() {
        return studentMapper.selectAll();
    }

    @Override
    public int disabledById(Integer id) {
        List<Login> logins = loginMapper.listByUserInfoId(id, (byte) 0);
        if (logins != null && logins.size() > 0) {
            return loginMapper.disable(logins.get(0).getId());
        }
        return 0;
    }

    @Override
    public int enableById(Integer id) {
        List<Login> logins = loginMapper.listByUserInfoId(id, (byte) 0);
        if (logins != null && logins.size() > 0) {
            return loginMapper.enable(logins.get(0).getId());
        }
        return 0;
    }

    @Override
    public Student selectByNumber(String number) {
        return studentMapper.selectByNumber(number);
    }

    @Override
    public String listStudentInfosByCalssId(Integer classId) {
        List<Student> studentList=studentMapper.listByClassId(classId);
        return JSONObject.toJSONString(studentList);
    }
}
